<?php
class Truckmodel extends CI_Model{
	public function saveUpload($insert_larn){
		$this->load->library('upload');

		//$insert_larn = $this->input->post('larn');

		$config['upload_path'] = './uploads_truck/'.$insert_larn.'/';
		if ( ! is_dir($config['upload_path'])){
			mkdir($config['upload_path'], 0777);
		}

		$config['allowed_types'] = 'csv';
		$config['file_name'] = date('d_m_Y H_i_s');
		//$config['overwrite'] = TRUE;

		$this->upload->initialize($config);
		if ($this->upload->do_upload('file1')){
			$success_data = $this->upload->data();
			$upload_path = base_url("uploads_truck/$insert_larn/{$success_data['orig_name']}");
		}else{
			echo 'error upload truck';
		}
		//print_r($upload_path);
		$this->db->set('larn_name', $insert_larn);
		$this->db->set('file1', $upload_path);
		$this->db->insert('truck_csv');

		return $upload_path;
	}

	function uploadCSV($csvpath,$larn){
		$header = array(
			'ticketid', 	//0.เลขที่ใบชั่ง
			'paydate', 	//1.วันที่จ่าย (handle korsor)
			'type_cash', 	//2.รูปแบบการซื้อ
			'truck_no', 	//3.ทะเบียนรถ
			'bill_no', 	//4.เลขที่ใบผ่าน
			'invoice', 	//5.เลขที่ใบกำกับ
			'car_no', 	//6.รถคันที่/วัน
			'cust_code', 	//7.รหัสผู้ขาย
			'cust_name', 	//8.ชื่อผู้ขาย
			'id_card', 	//9.เลขที่บัตรประชาชน
			'h_no', 	//10.บ้านเลขที่
			'h_moo', 	//11.หมู่ที่
			'h_tb', 	//12.ตำบล
			'h_ap', 	//13.อำเภอ
			'h_jv', 	//14.จังหวัด
			'h_post', 	//15.รหัสไปรษณีย์
			'h_tel', 	//16.เบอร์โทรศัพท์
			'h_fax', 	//17.เบอร์แฟ็กซ์
			'cred_group', 	//18.กลุ่มผู้ขาย
			'prod_code', 	//19.รหัสวัตถุดิบ
			'prod_name', 	//20.ชื่อวัตถุดิบ
			'grade', 	//21.กลุ่ม % แป้ง
			'brand', 	//22.คุณภาพวัตถุดิบ
			'prod_group', 	//23.พันธุ์หัวมัน
			'destn', 	//24.สถานที่เก็บวัตถุดิบ
			'unit1', 	//25.หน่วยนับ
			'weght_unt', 	//26.น.น./หน่วย
			'percen1', 	//27.% แป้งครั้งที่1
			'percen2', 	//28.% แป้งครั้งที่2
			'percen3', 	//29.% แป้งครั้งที่3
			'percen4', 	//30.% แป้งครั้งที่4
			'percen5', 	//31.% แป้งครั้งที่5
			'percen6', 	//32.% แป้งครั้งที่6
			'percen', 	//33.% แป้งเฉลี่ย
			'priceref', 	//34.ราคาอ้างอิง
			'sumpang', 	//35.น้ำหนักแป้ง (ก.ก.)
			'date_in', 	//36.วันที่เข้า
			'time_in', 	//37.เวลาเข้า
			'load_in', 	//38.น.น.เข้า
			'date_out', 	//39.วันที่ออก
			'time_out', 	//40.เวลาออก
			'load_out', 	//41.น.น.ออก
			'net_wght', 	//42.น.น.สุทธิ
			'time_total', 	//43.เวลารวม
			'delground', 	//44.หักดิน (%)
			'delground1', 	//45.รวมหักดิน (ก.ก.)
			'delmixer', 	//46.หักสิ่งเจือปน [%]
			'delmixer1', 	//47.รวมหักสิ่งเจือปน [ก.ก.]
			'delother', 	//48.หักอื่นๆ [%]
			'delother1', 	//49.รวมหักอื่นๆ [ก.ก.]
			'totalcut', 	//50.รวมหักน้ำหนัก
			'net_wght1', 	//51.น.น.คงเหลือ
			'cost_unt', 	//52.ราคา/หน่วย
			'buynet', 	//53.เงินซื้อสุทธิ
			'cost_dump', 	//54.ค่าดั้ม/ก.ก.
			'dump', 	//55.รวมเงินค่าดั้ม
			'deladvance', 	//56.หักอื่นๆ (เบิก)
			'totalcut_price', 	//57.รวมหักเงิน
			'buynet1', 	//58.เงินคงเหลือจ่าย
			'oil_unit', 	//59.ค่าขนส่ง / ก.ก.
			'oil_price', 	//60.รวมค่าขนส่ง
			'break', 	//61.ค่าเบรค/ก.ก.
			'cost_break', 	//62.ค่าเบรค
			'promotion_name', 	//63.รายการโปรโมชั่น
			'quantity_prom', 	//64.จำนวน   เก็บเงิน
			'promotion_unit', 	//65.หน่วยนับ
			'reprint', 	//66.พิมพ์ครั้งที่
			'user_login', 	//67.พนักงานชั่ง
			'remark', 	//68.หมายเหตุ
			'status', 	//69.สถานะรถ
			'iso', 	//70.ISO
			'extra_paymentdate', 	//71.extra-paymentDate
			'extra_carindate', 	//72.extra-carinDate
			'extra_caroutdate', 	//73.extra-caroutDate
			'vendor_land_id', //74
			'vendorid',		//75.user id
			'landcropid',		//76.subland id
			'larn',		//77
			'yymm', //78
			'docuno_run_no',	//79
			'docuno', //80
			'dateupload' //81
		);
		
		//get datetime upload
		$this->db->select('now() as dtupload',FALSE);
		$dt = $this->db->get();
		echo $this->db->last_query();
		$tmpdt = $dt->row_array();
		$dateupload = $tmpdt['dtupload'];
		
		if (($handle = fopen("$csvpath", "r")) !== FALSE) {
			$columns = fgetcsv($handle, 100000, ",");

			$insert_query_prefix = "INSERT IGNORE INTO TRUCK (".join(",",$header).")\nVALUES";
			while (($data = fgetcsv($handle, 100000, ",")) !== FALSE) {
				while (count($data) < count($header)){
					array_push($data, NULL);
				}
				
				foreach ($data as &$value){
					//cut all comma in integer value
					$value = str_replace(",", "", $value);
				}
					
				//vendorid + landid from custname
				$data[74] = $data[8];
				list($vendorcode,$sublandid) = explode('-',$data[74],2);
				$data[75] = $vendorcode;
				$data[76] = $sublandid;
				
				//larn
				$data[77] = $larn;
				
				//year2digit
				list($splitday, $splitmonth, $splityear) = explode('/', $data[1],3);
				$month2dig = str_pad($splitmonth, 2, "0", STR_PAD_LEFT);
				$year2dig = substr($splityear+543, 2);
				$data[78] = $year2dig.$month2dig;
				
				//71 extrapaymentdate from 1 paymentdate
				if ( ! empty($data[1]))
				$data[71] = DateTime::createFromFormat('d/m/Y', $data[1])->format('Y-m-d');
					
				//72 extracarindate from 36 datein 37 timein
				if ( ! empty($data[36]) && ! empty($data[37]) )
				$data[72] = DateTime::createFromFormat('d/m/YH:i:s', $data[36].$data[37])->format('Y-m-d H:i:s');
					
				//73 extracaroutdate from 39 dateout 40 timeout
				if ( ! empty($data[39]) && ! empty($data[40]))
				$data[73] = DateTime::createFromFormat('d/m/YH:i:s', $data[39].$data[40])->format('Y-m-d H:i:s');
					
				//81 dateupload
				$data[81] = $dateupload;
				
				
				$query = "$insert_query_prefix (".join(",",$this->quote_all_array($data)).");";
				//echo $query;
					
				$this->db->query($query);
				
				//update sum village accumulate
				//$this->updateSumVillage($data[75],$dateupload,$data[51],$data[58],$data[64]);
			
				
			}
			fclose($handle);
			
			//save sum of this upload by larn, dateupload
			$ins_id = $this->saveSumUpload($larn, $dateupload);
			//echo 'insert id = '.$ins_id;
			
			
		}
	}
	
	//every truck record
	function saveSumVillageByUpload($larn,$dateupload){
		
		$this->db->select('village,
							sum(net_wght1) as total_netwght,
							sum(buynet1) as total_netbuy,
							');
		$this->db->from('truck');
		$this->db->join('vendor','truck.vendorid = vendor.vendorcode');
		$this->db->join('vendor_address','vendor.uuid = vendor_address.uuid');
		$this->db->where('larn',$larn);
		$this->db->where('dateupload',$dateupload);
		$this->db->group_by('village');
		$tobesave = $this->db->get()->result_array();
		
		foreach ($tobesave as $village){
			$insertsumvillage = array(
			'villageid' => $village['village'] ,
			'dateupload' => $dateupload ,
			'sum_netwg' => $village['total_netwght'] ,
			'sum_netbuy' => $village['total_netbuy'],
			'sum_grouppromo' => $village['total_netbuy']*0.01,
		);
		$this->db->insert('truck_group_pay',$insertsumvillage);
		
		}
		
//		$this->db->select('village');
//		$this->db->from('truck');
//		$this->db->join('vendor', 'truck.vendorid = vendor.vendorcode');
//		$this->db->join('vendor_address', 'vendor.uuid = vendor_address.uuid');
//		$this->db->where('vendorid',$vendorcode);
//		$tmpquery = $this->db->get();
//		$row = $tmpquery->row_array();
//		$updatevillage = $row['village'];
//		
//		$sum_grouppromo = $sum_netbuy * 0.01;
//		if (empty($sum_promo)) $sum_promo = 0;
//		$sql = "INSERT INTO truck_group_pay (villageid,dateupload,sum_netwg,sum_netbuy,sum_promo,sum_grouppromo) 
//				VALUES ($updatevillage,'$dateupload',$sum_netwg,$sum_netbuy,$sum_promo,$sum_grouppromo)
//  				ON DUPLICATE KEY UPDATE sum_netwg=sum_netwg+$sum_netwg , sum_netbuy=sum_netbuy+$sum_netbuy ,
//  				sum_promo=sum_promo+$sum_promo, sum_grouppromo=sum_grouppromo+$sum_grouppromo";
//		$this->db->query($sql);
//		echo $this->db->last_query();
	}
	
	//every truck upload
	function saveSumUpload($larn,$dateupload){
		$this->db->select('dateupload, larn, sum(buynet) as totalbuy, sum(buynet1) as totalbuynet, sum(net_wght) as totalnetwght, sum(quantity_prom) as totalpromo',FALSE);
		$this->db->from('truck');
		$this->db->where('larn',$larn);
		$this->db->where('dateupload',$dateupload);
		$query = $this->db->get();
	
		$data = $query->row_array();
		//if truck is uploaded jingjing
		if ($data['dateupload']){
		$sum_netbuy_promo = $data['totalbuynet'] + $data['totalpromo'];
		$insertsumtruck = array(
			'larn' => $data['larn'] ,
			'dateupload' => $data['dateupload'] ,
			'sum_netwg' => $data['totalnetwght'] ,
			'sum_buy' => $data['totalbuy'],
			'sum_netbuy' => $data['totalbuynet'],
			'sum_promo' => $data['totalpromo'],
			'sum_netbuy_promo' => $sum_netbuy_promo,
		);

		$objreturn = $this->db->insert('truck_sum_upload',$insertsumtruck);
		
		$this->updateSumLarn($data['larn'], $data['totalbuy'], $data['totalbuynet'], $data['totalpromo'], $sum_netbuy_promo);
		
		//saveupload by village
		$this->saveSumVillageByUpload($larn, $dateupload);
		}
		
	}
	
	//
	function updateSumLarn($larn,$sum_buy,$sum_netbuy,$sum_promo,$sum_netbuy_promo){
		$sql = "INSERT INTO truck_larn_pay (larn,sum_buy,sum_netbuy,sum_promo,sum_netbuy_promo) 
				VALUES ($larn,$sum_buy,$sum_netbuy,$sum_promo,$sum_netbuy_promo)
  				ON DUPLICATE KEY UPDATE sum_buy=sum_buy+$sum_buy , sum_netbuy=sum_netbuy+$sum_netbuy ,
  				sum_promo=sum_promo+$sum_promo, sum_netbuy_promo=sum_netbuy_promo+$sum_netbuy_promo";
		$this->db->simple_query($sql);
	}


	function quote_all_array($values) {
		foreach ($values as $key=>$value)
		if (is_array($value))
		$values[$key] = $this->quote_all_array($value);
		else
		$values[$key] = $this->quote_all($value);
		return $values;
	}

	function quote_all($value) {
		if (is_null($value))
		return "NULL";

		$value = "'" . mysql_real_escape_string($value) . "'";
		return $value;
	}
}